﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces
{
    public class ProportionSummaryService:SqlSugarBase<ProportionSummary>, IProportionSummaryService
    {
        public ProportionSummaryService(IConfiguration _configuration)
        {
            base.connectionString = _configuration.GetConnectionString("DB1");
        }

        /// <summary>
        /// 机检确认报表(汇总占比)
        /// </summary>
        /// <returns></returns>
        public List<ProportionSummary> GetEquipmentDropBoxProportionSummary() 
        {
                string startTime = DateTime.Now.AddHours(-2).ToString("yyyy-MM-dd HH:mm:ss");
                string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                string sql = @"SELECT
	altb.CUFXEQU FXRESOURCENAME,
    COUNT(ccd.CONTAINERNAME) CONTAINERNAMECOUNT,
	SUM((CASE
	WHEN ccd.CUCONTAINERGRADERRECORDNAME LIKE 'RNP%' THEN 0
	WHen ccd.CUCONTAINERGRADERRECORDNAME LIKE 'RCF%' THEN 0
	ELSE TO_NUMBER(altb.CUREALQTY)
	END
	)) THEORYPIECE,
	SUM(altb.CUPIECECOUNT) INPIECECOUNT,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAPLUSGRADER1,0))/SUM(altb.CUPIECECOUNT), 4)*100, 'fm90.09'
	) ||'%' CUAPLUSGRADER1,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAPLUSGRADER2,0))/SUM(altb.CUPIECECOUNT), 4)*100, 'fm90.09'
	) ||'%' CUAPLUSGRADER2,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAPLUSGRADER3,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAPLUSGRADER3,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAGRADERWP,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAGRADERWP,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAGRADERBB,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAGRADERBB,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAGRADERXH,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAGRADERXH,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAGRADERQQ,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAGRADERQQ,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUAGRADER,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUAGRADER,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERGL,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERGL,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERXH,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERXH,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERQQ,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERQQ,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERHB,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERHB,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERTTV,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERTTV,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERDZ,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERDZ,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUBGRADERCC,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUBGRADERCC,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUCGRADERBG,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUCGRADERBG,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUCGRADEDIANZU,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUCGRADEDIANZU,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUCGRADERCC,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUCGRADERCC,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUDGRADERCK,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUDGRADERCK,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUDGRADERYL,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUDGRADERYL,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUGRADERQJ,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUGRADERQJ,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.CUUNKNOWGRADER,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' CUUNKNOWGRADER,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuGraderDD,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuGraderDD,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuAGraderHP,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuAGraderHP,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuBGraderHP,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuBGraderHP,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuDGraderPP,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuDGraderPP,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuDTTV,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuDTTV,
	TO_CHAR(
	ROUND(SUM(NVL(ccd.cuDHouBo,0))/SUM(altb.CUPIECECOUNT),4)*100, 'fm90.09'
	) ||'%' cuDHouBo
FROM CUCONTAINERGRADERRECORD ccd
LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID
WHERE ccd.SPECNAME IN ('分选') 
AND ccd.RECORDTIME>= to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND ccd.RECORDTIME<= to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss')
  GROUP BY altb.CUFXEQU
 ORDER BY DECODE(SUBSTR(altb.CUFXEQU, 0, 1), 'Q', 1, 'W', 2, '2', 3, '3', 4) ASC,
				 SUBSTR(altb.CUFXEQU, -2, 2) ASC
";
                return base.Query(sql);
        
        }
    }
}
